﻿using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SQLite
{
    /// <summary>
    /// SqliteConnection扩展
    /// </summary>
    internal static class ExSqliteConnection
    {

        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="dicParameters">字典集合 key：参数名；value：参数值</param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(this SqliteConnection connection, string commandText, Dictionary<string, object> dicParameters = null) => ExecuteNonQuery(connection, commandText, dicParameters.GreateParametersList());

        /// <summary>
        ///  对连接执行 Transact-SQL （参数化查询）语句并返回受影响的行数。
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="commandText"></param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(this SqliteConnection connection, string commandText, IEnumerable<SqliteParameter> parameters = null)
        {
            using (var transaction = connection.BeginTransaction())
            {
                using (var command = connection.CreateCommand())
                {
                    command.Transaction = transaction;
                    command.CommandText = commandText;

                    if (parameters != null && parameters.Count() > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    try
                    {
                        var result = command.ExecuteNonQuery();
                        transaction.Commit();
                        return result;
                    }
                    catch (SqliteException ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                }
            }
        }

        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回结果集中的第1行第1列,而忽略其他的行和列。
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="commandText"></param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(this SqliteConnection connection, string commandText, IEnumerable<SqliteParameter> parameters = null)
        {
            using (var transaction = connection.BeginTransaction())
            {
                using (var command = connection.CreateCommand())
                {
                    command.Transaction = transaction;
                    command.CommandText = commandText;

                    command.CommandText = commandText;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    try
                    {
                        var result = command.ExecuteScalar();
                        transaction.Commit();
                        return result;
                    }
                    catch (SqliteException ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                }
            }
        }


        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回结果集中的第1行第1列,而忽略其他的行和列。
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="commandText"></param>
        /// <param name="parameters">参数集合</param>
        /// <returns></returns>
        public static T ExecuteScalar<T>(this SqliteConnection connection, string commandText, IEnumerable<SqliteParameter> parameters = null) => (T)connection.ExecuteScalar(commandText, parameters);



        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回结果集中的第1行第1列,而忽略其他的行和列。
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="commandText"></param>
        /// <param name="dicParameters">字典集合 key：参数名；value：参数值</param>
        /// <returns></returns>
        public static T ExecuteScalar<T>(this SqliteConnection connection, string commandText, Dictionary<string, object> dicParameters = null) => (T)connection.ExecuteScalar(commandText, dicParameters.GreateParametersList());



        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回一个SqliteDataReader对象。
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="commandText"></param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static SqliteDataReader ExecuteReader(this SqliteConnection connection, string commandText, IEnumerable<SqliteParameter> parameters = null)
        {
            using (var transaction = connection.BeginTransaction())
            {
                using (var command = connection.CreateCommand())
                {
                    command.Transaction = transaction;
                    command.CommandText = commandText;

                    command.CommandText = commandText;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    try
                    {
                        var result = command.ExecuteReader();
                        transaction.Commit();
                        return result;
                    }
                    catch (SqliteException ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                }
            }
        }

        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回一个SqliteDataReader对象。
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="commandText"></param>
        /// <param name="action"></param>
        /// <param name="parameters"></param>
        public static void ExecuteReader(this SqliteConnection connection, string commandText, Action<SqliteConnection, SqliteDataReader> action, IEnumerable<SqliteParameter> parameters = null)
        {
            using (var transaction = connection.BeginTransaction())
            {
                using (var command = connection.CreateCommand())
                {
                    command.Transaction = transaction;
                    command.CommandText = commandText;

                    command.CommandText = commandText;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    try
                    {
                        var result = command.ExecuteReader();
                        transaction.Commit();
                        action?.Invoke(connection, result);
                    }
                    catch (SqliteException ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                }
            }
        }


        /// <summary>
        /// 通过字典集合创建 SQLiteParameter 集合
        /// </summary>
        /// <param name="dicParameters">字典集合 key：参数名；value：参数值</param>
        /// <returns></returns>
        private static List<SqliteParameter> GreateParametersList(this Dictionary<string, object> dicParameters)
        {
            if (dicParameters == null) return null;
            return dicParameters.Select(c => new SqliteParameter(c.Key.StartsWith("@") ? c.Key : ("@" + c.Key), c.Value)).ToList();
        }


    }

}
